package com.pi.wms.repository;

import com.pi.common.CodesConstants;
import com.pi.common.utils.DatabaseUtils;
import com.pi.framework.core.repository.IBaseRepository;
import com.pi.framework.core.util.DateUtils;
import com.pi.framework.core.util.StringUtils;
import com.pi.wms.entity.TwtActTransList;

import java.util.Map;


public interface KanbanReportRepository extends IBaseRepository<TwtActTransList, Long>{



//------------------------------------------------------------------------------------------------------------------
//  入库汇总
//------------------------------------------------------------------------------------------------------------------




//------------------------------------------------------------------------------------------------------------------
//  DEMO
//------------------------------------------------------------------------------------------------------------------

    public static String nativeSummaryDailyData(Map<String, Object> query, String databaseType){
		StringBuffer buf = new StringBuffer();
		buf.append(nativeSummaryDailyDataSelect(query, databaseType));
		buf.append(nativeSummaryDailyDataOrderby(query, databaseType));
		
		return buf.toString();
	}

	public static String nativeSummaryDailyDataSelect(Map<String, Object> query, String databaseType){
		Long clientid = null;
		Long divisionid = null;
		String transDate = "";
		String type = "";
		String material="";
		String specmodel="";
		try{
			if (query.get("globalClientId")!=null){
				clientid=(Long) query.get("globalClientId");
			}
			if (query.get("divisionid")!=null){
				divisionid=(Long) query.get("divisionid");
			}
			if (query.get("transDate")!=null){
				transDate= (String) query.get("transDate");
			}
			if (query.get("type")!=null){
				type= (String) query.get("type");
			}
            if (query.get("material")!=null){
                material= (String) query.get("material");
            }
            if (query.get("specmodel")!=null){
                specmodel= (String) query.get("specmodel");
            }

		}catch(Exception e){
			//
		}
		String dbtype=databaseType.toUpperCase();
		StringBuffer buf = new StringBuffer();
		buf.append("select ");
		if (dbtype.indexOf(CodesConstants.DATABASE_ORACLE)>=0){
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "trans_date", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }else if (dbtype.indexOf(CodesConstants.DATABASE_MSSQL)>=0){
            buf.append(DatabaseUtils.mssqlNativeDate("t1.open_time", "trans_date"));
            buf.append(",");
		}else if (dbtype.indexOf(CodesConstants.DATABASE_MYSQL)>=0){
			buf.append(DatabaseUtils.mysqlNativeDate("t1.open_time", "trans_date"));
			buf.append(",");
        }else {
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "trans_date", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }
		buf.append("  t1.material_id, t1.material_code, t1.material_name, ");
		buf.append("  t1.from_lotattr05 as lotnumber, ");
        buf.append("  t2.spec_model, t2.print_uom, ");
		buf.append("  count(t1.trans_id) as summ_trans, ");
		buf.append("  count(t1.from_trace_number) as summ_trace, ");
		buf.append("  count(distinct t1.from_contain_number) as summ_cont, ");
		buf.append("  sum(t1.qty_trans) as summ_qty ");
		buf.append("from twt_act_trans_list    t1 ");
		buf.append("left join twm_bas_material t2 on t1.material_id=t2.material_id ");
		buf.append("where t1.trans_status in ('0000','0880') ");
		if (clientid!=null){
			buf.append("  and t1.gclient_id=").append(String.valueOf(clientid)).append(" ");
		}
		if (divisionid!=null){
			buf.append("  and t1.division_id=").append(String.valueOf(divisionid)).append(" ");
		}
		if (dbtype.indexOf(CodesConstants.DATABASE_ORACLE)>=0){
			buf.append(" and ");
			buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
			buf.append(" like '%").append(transDate.trim()).append("%' ");
		}else if (dbtype.indexOf(CodesConstants.DATABASE_MSSQL)>=0){
			buf.append(" and ");
			buf.append(DatabaseUtils.mssqlNativeDate("t1.open_time", ""));
			buf.append(" like '%").append(transDate.trim()).append("%' ");
		}else if (dbtype.indexOf(CodesConstants.DATABASE_MYSQL)>=0){
			buf.append(" and ");
			buf.append(DatabaseUtils.mysqlNativeDate("t1.open_time", ""));
			buf.append(" like '%").append(transDate.trim()).append("%' ");
		}else {
			buf.append(" and ");
			buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
			buf.append(" like '%").append(transDate.trim()).append("%' ");
		}
		if (!StringUtils.isNullOrBlank(type)){
			buf.append(" and ");
			buf.append("t1.trans_type in (").append(getTypesText(type)).append(") ");
		}

        if (!StringUtils.isNullOrBlank(material)){
            material=material.trim();
            buf.append(" and ");
            buf.append(" (t2.material_code like '%").append(material).append("%' or t2.material_name like '%").append(material).append("%'  ) ");
        }

        if (!StringUtils.isNullOrBlank(specmodel)){
            specmodel=specmodel.trim();
            buf.append(" and ");
            buf.append(" (t2.spec_model like '%").append(specmodel).append("%' ) ");
        }

		buf.append("group by ");
        if (dbtype.indexOf(CodesConstants.DATABASE_ORACLE)>=0){
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }else if (dbtype.indexOf(CodesConstants.DATABASE_MSSQL)>=0){
            buf.append(DatabaseUtils.mssqlNativeDate("t1.open_time", ""));
            buf.append(",");
		}else if (dbtype.indexOf(CodesConstants.DATABASE_MYSQL)>=0){
			buf.append(DatabaseUtils.mysqlNativeDate("t1.open_time", ""));
			buf.append(",");
        }else {
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }
		buf.append("  t1.material_id, t1.material_code, t1.material_name, t1.from_lotattr05, ");
		buf.append("  t2.spec_model, t2.print_uom ");
		//buf.append("order by convert(varchar,t1.open_time,23),t1.material_code ");
		
		return buf.toString();
	}

	public static String nativeSummaryDailyDataOrderby(Map<String, Object> query, String databaseType){
		StringBuffer buf = new StringBuffer();
		//buf.append("order by convert(varchar,t1.open_time,23),t1.material_code ");

        String dbtype=databaseType.toUpperCase();
        buf.append("order by ");
        if (dbtype.indexOf(CodesConstants.DATABASE_ORACLE)>=0){
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }else if (dbtype.indexOf(CodesConstants.DATABASE_MSSQL)>=0){
            buf.append(DatabaseUtils.mssqlNativeDate("t1.open_time", ""));
            buf.append(",");
		}else if (dbtype.indexOf(CodesConstants.DATABASE_MYSQL)>=0){
			buf.append(DatabaseUtils.mysqlNativeDate("t1.open_time", ""));
			buf.append(",");
        }else {
            buf.append(DatabaseUtils.oracleNativeDate("t1.open_time", "", DateUtils.FORMAT_DATE_YYYY_MM_DD));
            buf.append(",");
        }
        buf.append("t1.material_code ");

		return buf.toString();
	}

	
	public static String getTypesText(String text){
		if (StringUtils.isNullOrBlank(text)){
			return "''";
		}
		String[] vals=text.split(",");
		StringBuffer buf=new StringBuffer();
		for(int i=0;i<vals.length;i++){
			if (vals[i]==null){
				continue;
			}
			buf.append("'").append(vals[i]).append("'");
			if (i<vals.length-1){
				buf.append(",");
			}
		}
		return buf.toString();
	}


}
